package rpt.yto.flex.remote.data.dao.processor;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.concurrent.ConcurrentHashMap;

import rpt.yto.flex.remote.data.bean.CorpBean;
import rpt.yto.flex.remote.data.bean.DeptdocBean;
import rpt.yto.flex.remote.data.biz.QueryBiz;
import rpt.yto.flex.remote.data.dao.itf.IPubObject;
import rpt.yto.util.ConvertFunc;


@SuppressWarnings({"unchecked"})
public class Query_allpsn_dept {

	
	private Map<String , String> distinctionMap = null;
	private StringBuilder builderJoinTransit;
	private Map<String , List<Map<String , Object>>> deptMap = null;
	
	private static Query_allpsn_dept querySqlFunc = new Query_allpsn_dept();
	
	private Query_allpsn_dept() {
		
	}
	
	public static final Query_allpsn_dept getInstance() {
		return querySqlFunc;
	}
	
	/**
	 *   全网按机构类型聚合表 - 全网人员类别
	 *   
	 * @param period
	 * @return
	 * @throws Exception
	 */
	public final StringBuilder getQueryTypeAll(String period) throws Exception {
		
		StringBuilder builderTypeAll = new StringBuilder();
		
		builderTypeAll.append(" select 1 rowno , '全网合计' statistictype , sum(endcount) endcount , sum(endcount_w) endcount_w ,  ");
		builderTypeAll.append(" sum(comcount) comcount , sum(mngcount) mngcount , sum(procount) procount , sum(procount_acc) procount_acc ,  ");
		builderTypeAll.append(" sum(skillcount) skillcount , sum(skillcount_acc) skillcount_acc , sum(expcount) expcount , sum(expcount_acc) expcount_acc ,  ");
		builderTypeAll.append(" sum(roundcount) roundcount , sum(roundcount_acc) roundcount_acc , sum(cscount) cscount from ( ");
		// 期末从业人员数
		builderTypeAll.append(" select count(1) endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeAll.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount "); 
		builderTypeAll.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeAll.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeAll.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeAll.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeAll.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeAll.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeAll.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeAll.append(" union all  ");
		// 期末女性从业人员数
		builderTypeAll.append(" select 0 endcount , count(1) endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeAll.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
		builderTypeAll.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeAll.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeAll.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeAll.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeAll.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeAll.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeAll.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeAll.append(" and bd_psnbasdoc.sex = '女' ");
		builderTypeAll.append(" union all  ");
		// 主管以上管理人员数
		builderTypeAll.append(" select 0 endcount , 0 endcount_w , count(1) comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeAll.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
		builderTypeAll.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeAll.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeAll.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeAll.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeAll.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeAll.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeAll.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeAll.append(" and (om_job.jobname like '%主管' or om_job.jobname like '%经理' or om_job.jobname like '%总监' or om_job.jobname like '%总裁' or om_job.jobname like '%高级专员'  or  om_job.jobname like '%专家') ");
		builderTypeAll.append(" union all  ");
		// 经理级以上人员数
		builderTypeAll.append(" select 0 endcount , 0 endcount_w , 0 comcount , count(1) mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeAll.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
		builderTypeAll.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeAll.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeAll.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeAll.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeAll.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeAll.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeAll.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeAll.append(" and (om_job.jobname like '%经理' or om_job.jobname like '%总监' or om_job.jobname like '%总裁'   or  om_job.jobname like '%专家') ");
		builderTypeAll.append(" union all  ");
		// 专业技术人员数
		// procount
		// 取得技术职称人员数
		// procount_acc
		// 技能人员数 
		// skillcount 
		// 取得技能资格证人员数
		// skillcount_acc
		// 快递业务人员数
		builderTypeAll.append(" select 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeAll.append(" 0 skillcount , 0 skillcount_acc , count(1) expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
		builderTypeAll.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeAll.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeAll.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeAll.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeAll.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeAll.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeAll.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeAll.append(" and (om_job.jobname like '%业务员') ");
		builderTypeAll.append(" union all  ");
		// 取得业务员资格证人员数
		builderTypeAll.append(" select 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeAll.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , count(1) expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
		builderTypeAll.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeAll.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeAll.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeAll.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeAll.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeAll.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeAll.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeAll.append(" and (om_job.jobname like '%业务员') and nvl(bd_psndoc.groupdef1 , 'N') = 'Y' ");
		builderTypeAll.append(" union all  ");
		// 分拣操作人员数
		builderTypeAll.append(" select 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeAll.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , count(1) roundcount , 0 roundcount_acc , 0 cscount ");
		builderTypeAll.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeAll.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeAll.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeAll.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeAll.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeAll.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeAll.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeAll.append(" and (om_job.jobname like '操作%') ");
		builderTypeAll.append(" union all  ");
		// 取得操作员资格证人员
		builderTypeAll.append(" select 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeAll.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , count(1) roundcount_acc , 0 cscount ");
		builderTypeAll.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeAll.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeAll.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeAll.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeAll.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeAll.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeAll.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeAll.append(" and (om_job.jobname like '操作%') and nvl(bd_psndoc.groupdef1 , 'N') = 'Y' ");
		builderTypeAll.append(" union all  ");
		// 客服人员数
		builderTypeAll.append(" select 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeAll.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , count(1) cscount ");
		builderTypeAll.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeAll.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeAll.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeAll.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeAll.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeAll.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeAll.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeAll.append(" and (om_job.jobname like '%客%') ) ");
		
		return builderTypeAll;
		
	}
	
	/**
	 * 全网按机构类型聚合表 - 直营/非直营机构人员类别
	 * 
	 * @param period
	 * @return
	 * @throws Exception
	 */
	public final StringBuilder getQueryTypeDirect(String period , Integer rowno , String statistictype , String isworkingunit) throws Exception {
		
		StringBuilder builderTypeDirect = new StringBuilder();
		
		builderTypeDirect.append(" select "+rowno+" rowno , '"+statistictype+"' statistictype , sum(endcount) endcount , sum(endcount_w) endcount_w ,  ");
		builderTypeDirect.append(" sum(comcount) comcount , sum(mngcount) mngcount , sum(procount) procount , sum(procount_acc) procount_acc ,  ");
		builderTypeDirect.append(" sum(skillcount) skillcount , sum(skillcount_acc) skillcount_acc , sum(expcount) expcount , sum(expcount_acc) expcount_acc ,  ");
		builderTypeDirect.append(" sum(roundcount) roundcount , sum(roundcount_acc) roundcount_acc , sum(cscount) cscount from ( ");
		// 期末从业人员数
		builderTypeDirect.append(" select count(1) endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeDirect.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount "); 
		builderTypeDirect.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeDirect.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeDirect.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeDirect.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeDirect.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeDirect.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeDirect.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeDirect.append(" and bd_corp.isworkingunit = '"+isworkingunit+"' ");
		builderTypeDirect.append(" union all  ");
		// 期末女性从业人员数
		builderTypeDirect.append(" select 0 endcount , count(1) endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeDirect.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
		builderTypeDirect.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeDirect.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeDirect.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeDirect.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeDirect.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeDirect.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeDirect.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeDirect.append(" and bd_psnbasdoc.sex = '女' ");
		builderTypeDirect.append(" and bd_corp.isworkingunit = '"+isworkingunit+"' ");
		builderTypeDirect.append(" union all  ");
		// 主管以上管理人员数
		builderTypeDirect.append(" select 0 endcount , 0 endcount_w , count(1) comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeDirect.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
		builderTypeDirect.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeDirect.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeDirect.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeDirect.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeDirect.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeDirect.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeDirect.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeDirect.append(" and (om_job.jobname like '%主管' or om_job.jobname like '%经理' or om_job.jobname like '%总监' or om_job.jobname like '%总裁'  or om_job.jobname like '%高级专员'  or  om_job.jobname like '%专家') ");
		builderTypeDirect.append(" and bd_corp.isworkingunit = '"+isworkingunit+"' ");
		builderTypeDirect.append(" union all  ");
		// 经理级以上人员数
		builderTypeDirect.append(" select 0 endcount , 0 endcount_w , 0 comcount , count(1) mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeDirect.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
		builderTypeDirect.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeDirect.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeDirect.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeDirect.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeDirect.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeDirect.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeDirect.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeDirect.append(" and (om_job.jobname like '%经理' or om_job.jobname like '%总监' or om_job.jobname like '%总裁'    or  om_job.jobname like '%专家') ");
		builderTypeDirect.append(" and bd_corp.isworkingunit = '"+isworkingunit+"' ");
		builderTypeDirect.append(" union all  ");
		// 专业技术人员数
		// procount
		// 取得技术职称人员数
		// procount_acc
		// 技能人员数 
		// skillcount 
		// 取得技能资格证人员数
		// skillcount_acc
		// 快递业务人员数
		builderTypeDirect.append(" select 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeDirect.append(" 0 skillcount , 0 skillcount_acc , count(1) expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
		builderTypeDirect.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeDirect.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeDirect.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeDirect.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeDirect.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeDirect.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeDirect.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeDirect.append(" and (om_job.jobname like '%业务员') ");
		builderTypeDirect.append(" and bd_corp.isworkingunit = '"+isworkingunit+"' ");
		builderTypeDirect.append(" union all  ");
		// 取得业务员资格证人员数
		builderTypeDirect.append(" select 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeDirect.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , count(1) expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
		builderTypeDirect.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeDirect.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeDirect.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeDirect.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeDirect.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeDirect.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeDirect.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeDirect.append(" and (om_job.jobname like '%业务员') and nvl(bd_psndoc.groupdef1 , 'N') = 'Y' ");
		builderTypeDirect.append(" and bd_corp.isworkingunit = '"+isworkingunit+"' ");
		builderTypeDirect.append(" union all  ");
		// 分拣操作人员数
		builderTypeDirect.append(" select 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeDirect.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , count(1) roundcount , 0 roundcount_acc , 0 cscount ");
		builderTypeDirect.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeDirect.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeDirect.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeDirect.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeDirect.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeDirect.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeDirect.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeDirect.append(" and (om_job.jobname like '操作%') ");
		builderTypeDirect.append(" and bd_corp.isworkingunit = '"+isworkingunit+"' ");
		builderTypeDirect.append(" union all  ");
		// 取得操作员资格证人员
		builderTypeDirect.append(" select 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeDirect.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , count(1) roundcount_acc , 0 cscount ");
		builderTypeDirect.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeDirect.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeDirect.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeDirect.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeDirect.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeDirect.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeDirect.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeDirect.append(" and (om_job.jobname like '操作%') and nvl(bd_psndoc.groupdef1 , 'N') = 'Y' ");
		builderTypeDirect.append(" and bd_corp.isworkingunit = '"+isworkingunit+"' ");
		builderTypeDirect.append(" union all  ");
		// 客服人员数
		builderTypeDirect.append(" select 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeDirect.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , count(1) cscount ");
		builderTypeDirect.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeDirect.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeDirect.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeDirect.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeDirect.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeDirect.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeDirect.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeDirect.append(" and bd_corp.isworkingunit = '"+isworkingunit+"' ");
		builderTypeDirect.append(" and (om_job.jobname like '%客%') ) ");
		
		return builderTypeDirect;
	}
	
	/**
	 * 
	 * 全网按机构类型聚合表 - distinctionMap中的类别
	 * 
	 * @param period
	 * @return
	 * @throws Exception
	 */
	public final StringBuilder getQueryTypeByMap(String period) throws Exception {
		
		if(distinctionMap == null)
			initDistinctionMap();
		
		StringBuilder builderTypeAppend2 = new StringBuilder();
		
		Iterator iter = distinctionMap.entrySet().iterator();
		int rowno = 3;
		while(iter.hasNext()) {
			
			Entry entry = (Entry) iter.next();
			String distinction = (String) entry.getKey();
			String statistictype = (String) entry.getValue();
			
			StringBuilder builderTypeByMap = new StringBuilder();
			
			builderTypeByMap.append(" select "+rowno+" rowno , '"+statistictype+"' statistictype , sum(endcount) endcount , sum(endcount_w) endcount_w ,  ");
			builderTypeByMap.append(" sum(comcount) comcount , sum(mngcount) mngcount , sum(procount) procount , sum(procount_acc) procount_acc ,  ");
			builderTypeByMap.append(" sum(skillcount) skillcount , sum(skillcount_acc) skillcount_acc , sum(expcount) expcount , sum(expcount_acc) expcount_acc ,  ");
			builderTypeByMap.append(" sum(roundcount) roundcount , sum(roundcount_acc) roundcount_acc , sum(cscount) cscount from ( ");
			// 期末从业人员数
			builderTypeByMap.append(" select count(1) endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
			builderTypeByMap.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount "); 
			builderTypeByMap.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
			builderTypeByMap.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
			builderTypeByMap.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
			builderTypeByMap.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
			builderTypeByMap.append("  and ((bd_psndoc.psnclscope = 0 ");
			builderTypeByMap.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
			builderTypeByMap.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
			builderTypeByMap.append(" and bd_corp.isworkingunit = 'Y' ");
			builderTypeByMap.append(" and bd_corp.unitdistinction in ( "+distinction+" ) ");
//			if("'ORGCORP00000000A0019'".equals(distinction)) builderTypeByMap.append(Query_allpsn.getInstance().getDirectDept());
			builderTypeByMap.append(" union all  ");
			// 期末女性从业人员数
			builderTypeByMap.append(" select 0 endcount , count(1) endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
			builderTypeByMap.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
			builderTypeByMap.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
			builderTypeByMap.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
			builderTypeByMap.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
			builderTypeByMap.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
			builderTypeByMap.append("  and ((bd_psndoc.psnclscope = 0 ");
			builderTypeByMap.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
			builderTypeByMap.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
			builderTypeByMap.append(" and bd_psnbasdoc.sex = '女' ");
			builderTypeByMap.append(" and bd_corp.isworkingunit = 'Y' ");
			builderTypeByMap.append(" and bd_corp.unitdistinction in ( "+distinction+" ) ");
//			if("'ORGCORP00000000A0019'".equals(distinction)) builderTypeByMap.append(Query_allpsn.getInstance().getDirectDept());
			builderTypeByMap.append(" union all  ");
			// 主管以上管理人员数
			builderTypeByMap.append(" select 0 endcount , 0 endcount_w , count(1) comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
			builderTypeByMap.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
			builderTypeByMap.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
			builderTypeByMap.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
			builderTypeByMap.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
			builderTypeByMap.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
			builderTypeByMap.append("  and ((bd_psndoc.psnclscope = 0 ");
			builderTypeByMap.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
			builderTypeByMap.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
			builderTypeByMap.append(" and (om_job.jobname like '%主管' or om_job.jobname like '%经理' or om_job.jobname like '%总监' or om_job.jobname like '%总裁'  or om_job.jobname like '%高级专员'  or  om_job.jobname like '%专家') ");
			builderTypeByMap.append(" and bd_corp.isworkingunit = 'Y' ");
			builderTypeByMap.append(" and bd_corp.unitdistinction in ( "+distinction+" ) ");
//			if("'ORGCORP00000000A0019'".equals(distinction)) builderTypeByMap.append(Query_allpsn.getInstance().getDirectDept());
			builderTypeByMap.append(" union all  ");
			// 经理级以上人员数
			builderTypeByMap.append(" select 0 endcount , 0 endcount_w , 0 comcount , count(1) mngcount , 0 procount , 0 procount_acc ,  ");
			builderTypeByMap.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
			builderTypeByMap.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
			builderTypeByMap.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
			builderTypeByMap.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
			builderTypeByMap.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
			builderTypeByMap.append("  and ((bd_psndoc.psnclscope = 0 ");
			builderTypeByMap.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
			builderTypeByMap.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
			builderTypeByMap.append(" and (om_job.jobname like '%经理' or om_job.jobname like '%总监' or om_job.jobname like '%总裁'   or  om_job.jobname like '%专家') ");
			builderTypeByMap.append(" and bd_corp.isworkingunit = 'Y' ");
			builderTypeByMap.append(" and bd_corp.unitdistinction in ( "+distinction+" ) ");
//			if("'ORGCORP00000000A0019'".equals(distinction)) builderTypeByMap.append(Query_allpsn.getInstance().getDirectDept());
			builderTypeByMap.append(" union all  ");
			// 专业技术人员数
			// procount
			// 取得技术职称人员数
			// procount_acc
			// 技能人员数 
			// skillcount 
			// 取得技能资格证人员数
			// skillcount_acc
			// 快递业务人员数
			builderTypeByMap.append(" select 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
			builderTypeByMap.append(" 0 skillcount , 0 skillcount_acc , count(1) expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
			builderTypeByMap.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
			builderTypeByMap.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
			builderTypeByMap.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
			builderTypeByMap.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
			builderTypeByMap.append("  and ((bd_psndoc.psnclscope = 0 ");
			builderTypeByMap.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
			builderTypeByMap.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
			builderTypeByMap.append(" and (om_job.jobname like '%业务员') ");
			builderTypeByMap.append(" and bd_corp.isworkingunit = 'Y' ");
			builderTypeByMap.append(" and bd_corp.unitdistinction in ( "+distinction+" ) ");
//			if("'ORGCORP00000000A0019'".equals(distinction)) builderTypeByMap.append(Query_allpsn.getInstance().getDirectDept());
			builderTypeByMap.append(" union all  ");
			// 取得业务员资格证人员数
			builderTypeByMap.append(" select 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
			builderTypeByMap.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , count(1) expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
			builderTypeByMap.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
			builderTypeByMap.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
			builderTypeByMap.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
			builderTypeByMap.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
			builderTypeByMap.append("  and ((bd_psndoc.psnclscope = 0 ");
			builderTypeByMap.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
			builderTypeByMap.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
			builderTypeByMap.append(" and (om_job.jobname like '%业务员') and nvl(bd_psndoc.groupdef1 , 'N') = 'Y' ");
			builderTypeByMap.append(" and bd_corp.isworkingunit = 'Y' ");
			builderTypeByMap.append(" and bd_corp.unitdistinction in ( "+distinction+" ) ");
//			if("'ORGCORP00000000A0019'".equals(distinction)) builderTypeByMap.append(Query_allpsn.getInstance().getDirectDept());
			builderTypeByMap.append(" union all  ");
			// 分拣操作人员数
			builderTypeByMap.append(" select 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
			builderTypeByMap.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , count(1) roundcount , 0 roundcount_acc , 0 cscount ");
			builderTypeByMap.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
			builderTypeByMap.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
			builderTypeByMap.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
			builderTypeByMap.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
			builderTypeByMap.append("  and ((bd_psndoc.psnclscope = 0 ");
			builderTypeByMap.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
			builderTypeByMap.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
			builderTypeByMap.append(" and (om_job.jobname like '操作%') ");
			builderTypeByMap.append(" and bd_corp.isworkingunit = 'Y' ");
			builderTypeByMap.append(" and bd_corp.unitdistinction in ( "+distinction+" ) ");
//			if("'ORGCORP00000000A0019'".equals(distinction)) builderTypeByMap.append(Query_allpsn.getInstance().getDirectDept());
			builderTypeByMap.append(" union all  ");
			// 取得操作员资格证人员
			builderTypeByMap.append(" select 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
			builderTypeByMap.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , count(1) roundcount_acc , 0 cscount ");
			builderTypeByMap.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
			builderTypeByMap.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
			builderTypeByMap.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
			builderTypeByMap.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
			builderTypeByMap.append("  and ((bd_psndoc.psnclscope = 0 ");
			builderTypeByMap.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
			builderTypeByMap.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
			builderTypeByMap.append(" and (om_job.jobname like '操作%') and nvl(bd_psndoc.groupdef1 , 'N') = 'Y' ");
			builderTypeByMap.append(" and bd_corp.isworkingunit = 'Y' ");
			builderTypeByMap.append(" and bd_corp.unitdistinction in ( "+distinction+" ) ");
//			if("'ORGCORP00000000A0019'".equals(distinction)) builderTypeByMap.append(Query_allpsn.getInstance().getDirectDept());
			builderTypeByMap.append(" union all  ");
			// 客服人员数
			builderTypeByMap.append(" select 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
			builderTypeByMap.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , count(1) cscount ");
			builderTypeByMap.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
			builderTypeByMap.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
			builderTypeByMap.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
			builderTypeByMap.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
			builderTypeByMap.append("  and ((bd_psndoc.psnclscope = 0 ");
			builderTypeByMap.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
			builderTypeByMap.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
			builderTypeByMap.append(" and bd_corp.isworkingunit = 'Y' ");
			builderTypeByMap.append(" and bd_corp.unitdistinction in ( "+distinction+" ) ");
//			if("'ORGCORP00000000A0019'".equals(distinction)) builderTypeByMap.append(Query_allpsn.getInstance().getDirectDept());
			builderTypeByMap.append(" and (om_job.jobname like '%客%') ) ");
			
			builderTypeAppend2.append(builderTypeByMap).append(" union all ");
			
			rowno ++;
			
		}
		
		builderTypeAppend2 = new StringBuilder(builderTypeAppend2.substring(0 , builderTypeAppend2.length() - " union all ".length()));
		
		return builderTypeAppend2;
	}
	
	public final StringBuilder getQueryTypeOper(String period) throws Exception {
		
		StringBuilder builderTypeOper = new StringBuilder();
		
		builderTypeOper.append(" select 9 rowno , '其中：加盟中转（中转操作人员）' statistictype , sum(endcount) endcount , sum(endcount_w) endcount_w ,  ");
		builderTypeOper.append(" sum(comcount) comcount , sum(mngcount) mngcount , sum(procount) procount , sum(procount_acc) procount_acc ,  ");
		builderTypeOper.append(" sum(skillcount) skillcount , sum(skillcount_acc) skillcount_acc , sum(expcount) expcount , sum(expcount_acc) expcount_acc ,  ");
		builderTypeOper.append(" sum(roundcount) roundcount , sum(roundcount_acc) roundcount_acc , sum(cscount) cscount from ( ");
		// 期末从业人员数
		builderTypeOper.append(" select count(1) endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeOper.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount "); 
		builderTypeOper.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeOper.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeOper.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeOper.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeOper.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeOper.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeOper.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeOper.append(" and ").append(getJoinTransit());
		builderTypeOper.append(" union all  ");
		// 期末女性从业人员数
		builderTypeOper.append(" select 0 endcount , count(1) endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeOper.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
		builderTypeOper.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeOper.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeOper.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeOper.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeOper.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeOper.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeOper.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeOper.append(" and bd_psnbasdoc.sex = '女' ");
		builderTypeOper.append(" and ").append(getJoinTransit());
		builderTypeOper.append(" union all  ");
		// 主管以上管理人员数
		builderTypeOper.append(" select 0 endcount , 0 endcount_w , count(1) comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeOper.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
		builderTypeOper.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeOper.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeOper.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeOper.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeOper.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeOper.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeOper.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeOper.append(" and (om_job.jobname like '%主管' or om_job.jobname like '%经理' or om_job.jobname like '%总监' or om_job.jobname like '%总裁'  or om_job.jobname like '%高级专员'  or  om_job.jobname like '%专家') ");
		builderTypeOper.append(" and ").append(getJoinTransit());
		builderTypeOper.append(" union all  ");
		// 经理级以上人员数
		builderTypeOper.append(" select 0 endcount , 0 endcount_w , 0 comcount , count(1) mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeOper.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
		builderTypeOper.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeOper.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeOper.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeOper.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeOper.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeOper.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeOper.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeOper.append(" and (om_job.jobname like '%经理' or om_job.jobname like '%总监' or om_job.jobname like '%总裁'    or  om_job.jobname like '%专家') ");
		builderTypeOper.append(" and ").append(getJoinTransit());
		builderTypeOper.append(" union all  ");
		// 专业技术人员数
		// procount
		// 取得技术职称人员数
		// procount_acc
		// 技能人员数 
		// skillcount 
		// 取得技能资格证人员数
		// skillcount_acc
		// 快递业务人员数
		builderTypeOper.append(" select 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeOper.append(" 0 skillcount , 0 skillcount_acc , count(1) expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
		builderTypeOper.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeOper.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeOper.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeOper.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeOper.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeOper.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeOper.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeOper.append(" and (om_job.jobname like '%业务员') ");
		builderTypeOper.append(" and ").append(getJoinTransit());
		builderTypeOper.append(" union all  ");
		// 取得业务员资格证人员数
		builderTypeOper.append(" select 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeOper.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , count(1) expcount_acc , 0 roundcount , 0 roundcount_acc , 0 cscount  ");
		builderTypeOper.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeOper.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeOper.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeOper.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeOper.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeOper.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeOper.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeOper.append(" and (om_job.jobname like '%业务员') and nvl(bd_psndoc.groupdef1 , 'N') = 'Y' ");
		builderTypeOper.append(" and ").append(getJoinTransit());
		builderTypeOper.append(" union all  ");
		// 分拣操作人员数
		builderTypeOper.append(" select 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeOper.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , count(1) roundcount , 0 roundcount_acc , 0 cscount ");
		builderTypeOper.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeOper.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeOper.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeOper.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeOper.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeOper.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeOper.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeOper.append(" and (om_job.jobname like '操作%') ");
		builderTypeOper.append(" and ").append(getJoinTransit());
		builderTypeOper.append(" union all  ");
		// 取得操作员资格证人员
		builderTypeOper.append(" select 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeOper.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , count(1) roundcount_acc , 0 cscount ");
		builderTypeOper.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeOper.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeOper.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeOper.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeOper.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeOper.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeOper.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeOper.append(" and (om_job.jobname like '操作%') and nvl(bd_psndoc.groupdef1 , 'N') = 'Y' ");
		builderTypeOper.append(" and ").append(getJoinTransit());
		builderTypeOper.append(" union all  ");
		// 客服人员数
		builderTypeOper.append(" select 0 endcount , 0 endcount_w , 0 comcount , 0 mngcount , 0 procount , 0 procount_acc ,  ");
		builderTypeOper.append(" 0 skillcount , 0 skillcount_acc , 0 expcount , 0 expcount_acc , 0 roundcount , 0 roundcount_acc , count(1) cscount ");
		builderTypeOper.append(" from bd_psndoc  , bd_psnbasdoc , om_job , bd_corp  ");
		builderTypeOper.append(" where bd_psndoc.pk_psnbasdoc = bd_psnbasdoc.pk_psnbasdoc(+) ");
		builderTypeOper.append(" and om_job.pk_om_job(+) = bd_psndoc.pk_om_job ");
		builderTypeOper.append(" and bd_corp.pk_corp(+) = bd_psndoc.pk_corp ");
		builderTypeOper.append("  and ((bd_psndoc.psnclscope = 0 ");
		builderTypeOper.append("  and bd_psndoc.outdutydate is null) or bd_psndoc.outdutydate > '"+period+"~') and bd_psndoc.indutydate <= '"+period+"~' ");
		builderTypeOper.append(" and (nvl(om_job.isabort , 'N') = 'N' or om_job.abortdate like '"+period+"%') and (bd_corp.isseal = 'N' or bd_corp.sealeddate >= '"+period+"~') ");
		builderTypeOper.append(" and ").append(getJoinTransit());
		builderTypeOper.append(" and (om_job.jobname like '%客%') ) ");
		
		return builderTypeOper;
	}
	
	private final void initDistinctionMap() throws Exception {
//		distinctionMap = new ConcurrentHashMap<String , String>();
//		distinctionMap.put("ORGCORP00000000A0015", "一、总公司");
//		distinctionMap.put("ORGCORP00000000A0016", "二、管理区");
//		distinctionMap.put("ORGCORP00000000A0017", "三、直营省区域公司");
//		distinctionMap.put("ORGCORP00000000A0018", "四、直营中心");
//		distinctionMap.put("ORGCORP00000000A0019", "五、直营公司");
		
		distinctionMap = Query_allpsn.getInstance().initDistinctionMap();
		
	}
	
	private final StringBuilder getJoinTransit() throws Exception {
		
		if(builderJoinTransit != null)
			return builderJoinTransit;
		
		List<Map<String , Object>> pk_corpList = (List<Map<String , Object>>) QueryBiz.getInstance().executeQuery(" select to_char(wmsys.wm_concat(chr(39) || pk_corp || chr(39))) pk_corp from bd_corp where unitcode in ("+ConvertFunc.getInstance().change(IPubObject.JOINCORP , "'" , "'" , ",")+") ", CorpBean.class);
		initJoinCorp((String) pk_corpList.get(0).get("pk_corp"));
		
		List<Map<String , Object>> resultList = deptMap.get(pk_corpList.get(0).get("pk_corp"));
		Map<Integer , String> indexMap = new ConcurrentHashMap<Integer , String>();
		StringBuilder builderPk_deptdoc = new StringBuilder();
		
		int total = 0;
		int row = 0;
		int index = 0;
		
		for(Map<String , Object> result : resultList) {
			builderPk_deptdoc.append(result.get("pk_deptdoc")).append(",");
			
			row ++;
			total ++;
			if(row % 500 == 0) {
				row = 0;
				indexMap.put(index, builderPk_deptdoc.substring(0 , builderPk_deptdoc.length() - 1));
				index ++;
				builderPk_deptdoc = new StringBuilder();
				
				continue;
			}
			
			if(resultList.size() == total) {
				
				indexMap.put(index, builderPk_deptdoc.substring(0 , builderPk_deptdoc.length() - 1));
				
			}
			
			
		}
		
		builderJoinTransit = new StringBuilder();
		builderJoinTransit.append(" bd_corp.unitcode in ("+ConvertFunc.getInstance().change(IPubObject.JOINCORP , "'" , "'" , ",")+") ");
		
		Iterator iter = indexMap.entrySet().iterator();
		while(iter.hasNext()) {
			Entry entry = (Entry) iter.next();
			builderJoinTransit.append(" and bd_psndoc.pk_deptdoc not in ("+entry.getValue()+") ");
		}
		
		builderJoinTransit.append(" and nvl(om_job.isabort , 'N') = 'N' ");
		return builderJoinTransit;
	}

	private final void initJoinCorp(String pk_corp) throws Exception {
		
		if(deptMap == null)
			deptMap = new ConcurrentHashMap<String , List<Map<String , Object>>>();
		
		if(deptMap.get(pk_corp) == null) {
			List<Map<String , Object>> deptList = (List<Map<String, Object>>) QueryBiz.getInstance().executeQuery("select deptcode from bd_deptdoc where length(deptcode) = 6 and pk_corp in ("+pk_corp+")", DeptdocBean.class);
	
			List<Map<String , Object>> pk_deptdocList = new ArrayList<Map<String , Object>>();
			for(Map<String , Object> dept : deptList) {
				
				String queryString = "select chr(39) || pk_deptdoc || chr(39) pk_deptdoc from bd_deptdoc where deptcode like '"+dept.get("deptcode")+"%' and pk_corp in ("+pk_corp+")";
				pk_deptdocList.addAll((List<Map<String, Object>>) QueryBiz.getInstance().executeQuery(queryString, DeptdocBean.class));
				
			}
			
			deptMap.put(pk_corp, pk_deptdocList);
		}
	}
	
	public final String getQuerySQL(String period) throws Exception {
		
		return getQueryTypeAll(period)
				.append(" union all ").append(getQueryTypeDirect(period , 2 , "其中：直营机构人员" , "Y"))
				.append(" union all ").append(getQueryTypeByMap(period))
				.append(" union all ").append(getQueryTypeDirect(period , 8 , "六、加盟公司" , "N"))
				.append(" union all ").append(getQueryTypeOper(period)).toString();
		
	}

}
